﻿-- Add Audit Tables
-- ========================================

/*
If you have an existing configuration manager database running the below script will add auditing at database level.

*/

/****** Object:  Table [dbo].[SolutionSettingChanges]    Script Date: 08/23/2011 13:08:16 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[SolutionSettingChanges](
	[ChangeID] [int] IDENTITY(1,1) NOT NULL,
	[ProjectID] [int] NOT NULL,
	[ProjectName] [nvarchar](255) NOT NULL,
	[SolutionID] [int] NOT NULL,
	[SolutionName] [nvarchar](255) NOT NULL,
	[SolutionSettingID] [int] NOT NULL,
	[SolutionSettingTag] [nvarchar](255) NOT NULL,
	[SolutionSettingDefaultValue] [nvarchar](512) NULL,
	[SolutionSettingDescription] [nvarchar](2048) NULL,
	[ChangedBy] [nvarchar](255) NOT NULL,
	[ChangedDateTime] [datetime] NOT NULL,
	[ChangeAction] [nvarchar] (50) NOT NULL
 CONSTRAINT [PK_SolutionSettingChanges] PRIMARY KEY CLUSTERED 
(
	[ChangeID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[SolutionSettingChanges] ADD  CONSTRAINT [DF_SolutionSettingChanges_ChangedDateTime]  DEFAULT (getdate()) FOR [ChangedDateTime]
GO


DROP TRIGGER SolutionSettingsAudit
GO

CREATE TRIGGER SolutionSettingsAudit 
    ON dbo.SolutionSettings 
    FOR INSERT, UPDATE, DELETE 
AS 
	Declare
		@ProjectID int,
		@ProjectName nvarchar(255),
		@SolutionID int,
		@SolutionName nvarchar(255),
		@SolutionSettingID int,
		@SolutionSettingTag nvarchar(512),
		@SolutionSettingDescription nvarchar(2048),
		@SolutionSettingDefaultValue nvarchar(512),
		@ChangedBy nvarchar(50),
		@ChangeDateTime datetime,
		@Action nvarchar(50)

	Set @ChangeDateTime = GETDATE()
	Set @ChangedBy = SUSER_NAME()
	
    IF (SELECT COUNT(*) FROM inserted) > 0 
    BEGIN 
        IF (SELECT COUNT(*) FROM deleted) > 0 
        BEGIN 
			-- Get Parameters
			SELECT  
				@Action = 'Update',
				@SolutionID = SolutionID,
				@SolutionSettingID = SolutionSettingID,
				@SolutionSettingTag = SettingTag,
				@SolutionSettingDescription = SettingDescription,
				@SolutionSettingDefaultValue = DefaultValue
            FROM 
				inserted  
        END 
        ELSE 
        BEGIN 
			-- Get Parameters
			SELECT  
				@Action = 'Insert',
				@SolutionID = SolutionID,
				@SolutionSettingID = SolutionSettingID,
				@SolutionSettingTag = SettingTag,
				@SolutionSettingDescription = SettingDescription,
				@SolutionSettingDefaultValue = DefaultValue
            FROM 
				inserted 
        END 
    END 
    ELSE 
    BEGIN 
		-- Get Parameters
			SELECT  
				@Action = 'Deleted',
				@SolutionID = SolutionID,
				@SolutionSettingID = SolutionSettingID,
				@SolutionSettingTag = SettingTag,
				@SolutionSettingDescription = SettingDescription,
				@SolutionSettingDefaultValue = DefaultValue
            FROM 
				deleted 
    END 
    
    -- Get Additional Data
    Select 
		@SolutionName = SolutionName,
		@ProjectID = ProjectID  
	From Solutions 
	Where SolutionID = @SolutionID
	
	Select 
		@ProjectName = ProjectName
	From Projects 
	Where ProjectID = @ProjectID
    
    -- Add to audit
    INSERT INTO [SolutionSettingChanges]
			(
				[ProjectID]
			   ,[ProjectName]
			   ,[SolutionID]
			   ,[SolutionName]
			   ,[SolutionSettingID]
			   ,[SolutionSettingTag]
			   ,[SolutionSettingDefaultValue]
			   ,[SolutionSettingDescription]
			   ,[ChangedBy]
			   ,[ChangedDateTime]
			   ,[ChangeAction]
			)
			VALUES
           (
				@ProjectID
			   ,@ProjectName
			   ,@SolutionID
			   ,@SolutionName
			   ,@SolutionSettingID
			   ,@SolutionSettingTag
			   ,@SolutionSettingDefaultValue
			   ,@SolutionSettingDescription
			   ,@ChangedBy
			   ,@ChangeDateTime
			   ,@Action
			) 
GO


/****** Object:  Table [dbo].[SolutionSettingChanges]    Script Date: 08/23/2011 13:08:16 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[SettingValuesChanges](
	[ChangeID] [int] IDENTITY(1,1) NOT NULL,
	[ProjectID] [int] NOT NULL,
	[ProjectName] [nvarchar](255) NOT NULL,
	[SolutionID] [int] NOT NULL,
	[SolutionName] [nvarchar](255) NOT NULL,
	[SolutionSettingID] [int] NOT NULL,
	[SolutionSettingTag] [nvarchar](255) NOT NULL,
	[EnvironmentID] [int] NOT Null,
	[EnvironmentName] [nvarchar](255) NOT NULL,
	[ChangedBy] [nvarchar](255) NOT NULL,
	[ChangedDateTime] [datetime] NOT NULL,
	[SettingValue] [nvarchar] (512) NOT NULL,
	[ChangeAction] [nvarchar] (50) NOT NULL,
CONSTRAINT [PK_SettingValuesChanges] PRIMARY KEY CLUSTERED 
(
	[ChangeID] ASC
)
WITH 
(
	PAD_INDEX  = OFF, 
	STATISTICS_NORECOMPUTE  = OFF, 
	IGNORE_DUP_KEY = OFF, 
	ALLOW_ROW_LOCKS  = ON, 
	ALLOW_PAGE_LOCKS  = ON
) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[SettingValuesChanges] ADD  CONSTRAINT [DF_SettingValuesChanges_ChangedDateTime]  DEFAULT (getdate()) FOR [ChangedDateTime]
GO

--Audit Trigger

DROP TRIGGER SettingValuesAudit
GO

CREATE TRIGGER SettingValuesAudit 
    ON dbo.SettingValues 
    FOR INSERT, UPDATE, DELETE 
AS 
	Declare
		@SettingValueID int,
		@ProjectID int,
		@ProjectName nvarchar(255),
		@SolutionID int,
		@SolutionName nvarchar(255),
		@SolutionSettingID int,
		@SolutionSettingTag nvarchar(512),
		@EnvironmentID int,
		@EnvironmentName nvarchar(255),
		@ChangedBy nvarchar(50),
		@ChangeDateTime datetime,
		@Action nvarchar(50),
		@SettingValue nvarchar(512)

	Set @ChangeDateTime = GETDATE()
	Set @ChangedBy = SUSER_NAME()
	
    IF (SELECT COUNT(*) FROM inserted) > 0 
    BEGIN 
        IF (SELECT COUNT(*) FROM deleted) > 0 
        BEGIN 
          -- Get Parameters
			SELECT  
				@Action = 'Updated',
				@SettingValueID = SettingValueID,
				@SolutionSettingID = SolutionSettingID,
				@EnvironmentID = EnvironmentID,
				@SettingValue = SettingValue
            FROM 
				inserted
        END 
        ELSE 
        BEGIN 
			-- Get Parameters
			SELECT  
				@Action = 'Inserted',
				@SettingValueID = SettingValueID,
				@SolutionSettingID = SolutionSettingID,
				@EnvironmentID = EnvironmentID,
				@SettingValue = SettingValue
            FROM 
				inserted 
        END 
    END 
    ELSE 
    BEGIN 
		 -- Get Parameters
			SELECT  
				@Action = 'Deleted',
				@SettingValueID = SettingValueID,
				@SolutionSettingID = SolutionSettingID,
				@EnvironmentID = EnvironmentID,
				@SettingValue = SettingValue
            FROM 
				deleted
    END 
    
    -- Get Additional Data
    Select
		@SolutionSettingTag = SettingTag,
		@SolutionID = SolutionID
	From SolutionSettings 
	Where SolutionSettingID = @SolutionSettingID 
	
    Select 
		@SolutionName = SolutionName,
		@ProjectID = ProjectID  
	From Solutions 
	Where SolutionID = @SolutionID
	
	Select 
		@ProjectName = ProjectName
	From Projects 
	Where ProjectID = @ProjectID
	
	Select 
		@EnvironmentName = EnvironmentName
	From Environments
	Where EnvironmentID = @EnvironmentID
			
	-- Add Audit Record
    INSERT INTO [SettingValuesChanges]
			   ([ProjectID]
			   ,[ProjectName]
			   ,[SolutionID]
			   ,[SolutionName]
			   ,[SolutionSettingID]
			   ,[SolutionSettingTag]
			   ,[EnvironmentID]
			   ,[EnvironmentName]
			   ,[ChangedBy]
			   ,[ChangedDateTime]
			   ,[ChangeAction]
			   ,[SettingValue])
			VALUES
			   (
			   @ProjectID
			   ,@ProjectName
			   ,@SolutionID
			   ,@SolutionName 
			   ,@SolutionSettingID
			   ,@SolutionSettingTag
			   ,@EnvironmentID
			   ,@EnvironmentName
			   ,@ChangedBy
			   ,@ChangeDateTime
			   ,@Action
			   ,@SettingValue
			   )
GO